<?php
/**
 * Created by PhpStorm.
 * User: apple
 * Date: 17/3/3
 * Time: 09:50
 */

namespace Warehouse\Model;

use Inbound\Model\CommoninterfaceModel;
use Inbound\Service\PrepareneedsService;

class InventoriesModel extends CommoninterfaceModel
{

    //数据库对象
    public $inventories = null;
    //收货明细表
    static $table = 'inventories';

    public $inventories_id;


    /**
     * @param string $table
     * @param array  $param
     */
    public function __construct($table = '', $param = array())
    {
        $this->inventories = M(self::$table, 'wms_', $this->_db);
        parent::__construct();
    }

    /**
     * @param array $_array
     *
     * @return mixed
     * 过滤查询条件
     */
    public function get_inventories_model(&$_array = array(), $flag = true)
    {

        if ($flag) {
            if ($_array) {
                $_array = array_filter($_array);
            }
            if (!empty($_array["print_status"]) and '-1' == $_array["print_status"]) {
                $_array["print_status"] = 0;
            }
            if (!empty($_array["export_tax_rebate"]) and '-1' == $_array["export_tax_rebate"]) {
                $_array["export_tax_rebate"] = 0;
            }
            $_array = PrepareneedsService::trim_array($_array);
            foreach ($_array as $key => $_arr) {
                switch ($key) {
                    case 'create_time_from':
                        $_array['invoice_date'][] = array(
                            'egt',
                            $_arr
                        );
                        unset($_array['create_time_from']);
                        break;
                    case 'create_time_to':
                        $_array['invoice_date'][] = array(
                            'elt',
                            $_arr
                        );
                        unset($_array['create_time_to']);
                        break;
                }
            }
            return $this->inventories->where($_array);
        } else {
            return $this->inventories->where($_array);
        }
    }

    /**
     * 非出口退税
     * 期初库存
     *
     * @param sku
     *
     * @return mixed
     */
    public function get_sku_open_count(&$_array)
    {
        $date      = date('Y-m-01', time());
        $model     = $this->inventories;
        $where_map = array(
            'sku'               => $_array['sku'],
            'enterprise_dominant' => $_array['enterprise_dominant'],
            'site_id' => $_array['site_id'],
            'inventory_date'    => $date,
            'export_tax_rebate' => 0,
        );

        $res       = $model->where($where_map)
            ->field('quantity, cost, sku, sku_name, sku_standard')
            ->find();
        return $res;
    }

    /**
     * 出口退税
     * 期初库存
     *
     * @param sku
     *
     * @return mixed
     */
    public function get_tax_sku_open_count(&$_array)
    {
        $date      = date('Y-m-01', time());
        $model     = $this->inventories;
        $where_map = array(
            'sku'               => $_array['sku'],
            'enterprise_dominant' => $_array['enterprise_dominant'],
            'site_id' => $_array['site_id'],
            'inventory_date'    => $date,
            'export_tax_rebate' => 1,
        );
        $res       = $model->where($where_map)
            ->field('quantity, cost, sku, sku_name, sku_standard')
            ->find();
        return $res;
    }
    public function get_sku_count($_array)
    {
        $model     = $this->inventories;
        $res       = $model->where($_array)
            ->getField('quantity');
        return $res;
    }

    //先删除当月已经存在的sku
    public function delete_inventories()
    {
        $model = $this->inventories;

        $starttime = date('Y-m-01', strtotime('-1 month'));

        $qimo_day = date('Y-m-01', strtotime("$starttime +1 month"));//下个月的第一天

        $sql_de = "DELETE FROM `wms_inventories` WHERE `inventory_date`='".$qimo_day."'";
        $model->execute($sql_de);
    }

    /**
     * 增加数据
     */
    public function addall_data(&$data = array())
    {
        $model = $this->inventories;

        return $this->inventories_id = $model->addAll($data);
    }

    /**
     * 更新库存信息
     *
     * @param $skus_arr
     * @param $taxflag
     */
    public function update_inventories(&$skus_arr, $taxflag)
    {
        $model = $this->inventories;

        $starttime = date('Y-m-01', strtotime('-1 month'));
        $endtime   = date('Y-m-t', strtotime('-1 month'));

        $qimo_day = date('Y-m-01', strtotime("$starttime +1 month"));//下个月的第一天

        $skus_str = '\'';
        $skus_str .= implode('\',\'', $skus_arr);
        $skus_str .= '\'';

        $sql_select = "SELECT upper(sku) as sku,sum(num) as num,sum(cost) as cost,
					sum(open_num) as open_num,sum(open_cost) as open_cost,
					sum(in_num) as in_num,sum(in_cost) as in_cost,sum(out_num) as out_num,sum(out_cost) as out_cost from (
				SELECT
					wms_warehouseorders.sku AS sku,
					wms_warehouseorders.warehouse_quantity AS num,
					wms_warehouseorders.cost AS cost,
					0 as open_num,
					0 as open_cost,
					wms_warehouseorders.warehouse_quantity AS in_num,
					wms_warehouseorders.cost AS in_cost,
					0 AS out_num,
					0 AS out_cost
				FROM
					wms_warehouseorders
				WHERE
					wms_warehouseorders.warehouse_date >= '".$starttime."'
				AND
					wms_warehouseorders.warehouse_date <= '".$endtime." 23:59:59'
				AND 
				    wms_warehouseorders.export_tax_rebate = $taxflag
				AND 
				    wms_warehouseorders.sku IN (".$skus_str.")
			    UNION ALL
				SELECT
					`wms_deliveryorders`.`sku` AS `sku` ,
					- (`wms_deliveryorders`.`quantity`) AS `num`,
					- (`wms_deliveryorders`.`cost`) AS `cost`,
					0 as open_num,
					0 as open_cost,
					0 AS in_num,
					0 AS in_cost,
					`wms_deliveryorders`.`quantity` AS out_num,
					`wms_deliveryorders`.`cost` AS out_cost
				FROM
					`wms_deliveryorders`
				WHERE
					`wms_deliveryorders`.`delivery_date` >= '".$starttime."'
				AND
					`wms_deliveryorders`.`delivery_date` <= '".$endtime." 23:59:59'
                AND 
                    wms_deliveryorders.export_tax_rebate = $taxflag
                AND
                    wms_deliveryorders.sku IN (".$skus_str.")
		    	UNION ALL
				SELECT
					`wms_inventories`.`sku` AS `sku`,
					`wms_inventories`.`quantity` AS `num`,
					`wms_inventories`.`cost` as `cost`,
					`wms_inventories`.`quantity` as open_num,
					`wms_inventories`.`cost` as open_cost,
					0 AS in_num,
					0 AS in_cost,
					0 AS out_num,
					0 AS out_cost
				FROM
					`wms_inventories`
				WHERE
					`wms_inventories`.`inventory_date` = '".$starttime."'
                AND 
                    wms_inventories.export_tax_rebate = $taxflag
                AND
                    wms_inventories.sku IN (".$skus_str.")
					) a where sku in (".$skus_str.") GROUP BY sku";

        $select         = $model->query($sql_select);
        $sql_update_num = $sql_update_cost = $sql_update_do = '';

        foreach ($select as $key => $_select) {
            $sql_update_num .= " WHEN '".$_select['sku']."' THEN '".$_select['num']."'";
            $add_temp = $_select['open_num'] + $_select['in_num'];
            //求sku的月末加权平均成本
            if ($add_temp != 0) {
                $_select['single_out_cost'] = ($_select['open_cost'] + $_select['in_cost']) / $add_temp;
            } else {

                $_select['single_out_cost'] = 0;
            }
            $_select['cost'] =
                $_select['open_cost'] + $_select['in_cost'] - $_select['single_out_cost'] * $_select['out_num'];
            $sql_update_cost .= " WHEN '".$_select['sku']."' THEN '".$_select['cost']."'";//更新库存成本
            $sql_update_do .= " WHEN '".$_select['sku']."' THEN quantity*".$_select['single_out_cost'];//更新出库单的成本
        }
        $sql_update = "UPDATE inventories SET quantity=(CASE sku ".$sql_update_num." END),
					cost=(CASE sku ".$sql_update_cost." END) WHERE sku in (".$skus_str.") AND inventory_date = '"
                      .$qimo_day."'";

        $model->execute($sql_update);

        $sql_up_do         = "UPDATE deliveryorders SET
					cost=(CASE sku ".$sql_update_do." END)
                   WHERE
                   delivery_date >='".$starttime."'
                   AND delivery_date<='".$endtime." 23:59:59'
                   AND sku IN (".$skus_str.")";
        $deliveroriesModel = M('deliveryorders', 'wms_', $this->_db);
        $deliveroriesModel->execute($sql_up_do);
    }

    /**
     * @return mixed
     * 获取上月期初库存
     * khq 2017.4.28
     */
    public function get_qichu_invent() {
        
        $starttime = date('Y-m-01', strtotime('-1 month'));
        $where['inventory_date'] = array('eq',$starttime);
        return $this->inventories->group( 'sku,enterprise_dominant,export_tax_rebate,site_id')
            ->where($where)
            ->field(
                'sku,enterprise_dominant,export_tax_rebate,site_id,sku_name,supplier_id,
                SUM(quantity) AS all_quantity,SUM(cost) AS all_cost,sku_standard'
            )
            ->select();
    }

}